const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");

// 政策兑付
router.post('/pay/zcdf', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let beginDate = formData.beginDate ? formData.beginDate: '2001-01-01';
    let endDate = formData.endDate?formData.endDate:tool.getDay();
    let buId = formData.buId?formData.buId:0;
    let salesId = formData.salesId?formData.salesId:0;
    let billNo = formData.billNo?formData.billNo:'';
    let pageSize = body.pageSize ?body.pageSize:200;
    let currentPage = body.currentPage ?body.currentPage:1;
    let skip = (currentPage - 1) *pageSize;
    let sort = body.sort ? body.sort : []
    let excel = body.excel
    let qustr = ' '
    if(buId) {
        qustr += ` and c.buId=${buId} `
    }
    let sqls = `
        with bills as (
            select buId,buName,1 isInit,N'期初余额' transTypeName, '' billNo,'' billDate,0 addYs,0 rpAmount,0 ysAmount,0 nowCheck,0 yhAmount,'' salesName,'' remark,
                   initYe ye from (
                                              select a.buId,co.name buName,sum(b.price2-b.price1) initYe
                                              from card a inner join card_info b on a.tenantId=b.tenantId and a.id=b.cardId  and a.billState='accounted'
                                                          left join contact co on a.tenantId=co.tenantId and a.buId=co.id
                                                          left join invoice c on a.tenantId=c.tenantId and a.byIid=c.id
                                              where a.tenantId=${tenantId} and a.billDate<='${beginDate}' 
                                                and b.subjectId in (select id from category where tenantId=${tenantId} and typeNumber='subject' and name in(N'应付账款',N'其他应付款')) 
                                              ${qustr}
                                              group by a.buId,co.name
                                          ) a
            union all
            select a.buId,co.name buName,0 isInit,iif(c.id is null,N'凭证',c.transTypeName) transTypeName,
                   iif(c.id is null,a.billNo,c.billNo) billNo,
                   iif(c.id is null,a.billDate,c.billDate) billDate,
                   iif(c.id is null,b.price1-b.price2,iif(c.billType in('RECEIPT','PAYMENT'),-c.totalAmount,c.amountReal)) addYs,
                   iif(c.id is null,0,c.rpAmountReal) rpAmount,
                   iif(c.id is null,0,c.ysAmountReal) ysAmount,
                   iif(c.id is null,0,c.nowCheck) nowCheck,
                   iif(c.id is null,0,c.yhAmount) yhAmount,c.salesName,c.remark,
                   0 initYe 
            from card a inner join card_info b on a.tenantId=b.tenantId and a.id=b.cardId
                and a.billState='accounted'
                        left join contact co on a.tenantId=co.tenantId and a.buId=co.id
                        left join v_main_withhx c on a.tenantId=c.tenantId and a.byIid=c.id
            where a.tenantId=${tenantId} and a.billDate between '${beginDate}' and '${endDate}' and b.subjectId in (select id from category where tenantId=${tenantId} and typeNumber='subject' and name in(N'应付账款',N'其他应付款'))
            ${qustr}
        ) select buId, buName, isInit, transTypeName, billNo, billDate, addYs, rpAmount, ysAmount, nowCheck, yhAmount, salesName,remark,
           iif(isInit=1,ye,addYs-rpAmount-ysAmount) ye,
           iif(transTypeName in(N'收款单',N'付款单'),0,iif(isInit=1,0,addYs-rpAmount-ysAmount-nowCheck-yhAmount)) leftYe 
           from bills order by buName,isInit desc
    `
    if(excel) {
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return tool.doExcelOut(ctx, list)
    }
    else {
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        let ret = []
        let gp = tool.xe.groupBy(list, 'buId')
        for(let i in gp) {
            let bus = gp[i]
            for (let j = 0; j < bus.length; j++) {
                if(j === 0) {
                    if(bus[j].isInit === 1) {
                        if(bus.length > 1) {
                            bus[j].buName = bus[1].buName
                        }
                        ret.push(bus[j])
                    }
                    else {
                        ret.push({
                            isInit: 1,
                            transTypeName: '期初余额',
                            buName: bus[j].buName,
                            buId: bus[j].buId,
                            salesId: bus[j].salesId,
                            billNo: '',
                            billDate: '',
                            addYs: 0,
                            yufAdd: 0,
                            ye: 0,
                            remark: ''
                        })
                        bus[j].buName = ''
                        ret.push(bus[j])
                    }
                }
                else {
                    bus[j].buName = ''
                    ret.push(bus[j])
                }
                if(j > 0) {
                    bus[j].ye = tool.xe.add(bus[j-1].ye,bus[j].ye)
                }

                let fk = tool.xe.sum(bus,'rpAmount')
                let ysAmount = tool.xe.sum(bus,'ysAmount')
                let sumys = tool.xe.sum(bus,'addYs')
                let sumyus = tool.xe.sum(bus,'addYus')
                let leftYe = tool.xe.sum(bus,'leftYe')
                let nowCheck = tool.xe.sum(bus,'nowCheck')
                let yhAmount = tool.xe.sum(bus,'yhAmount')
                if(j === bus.length - 1) {
                    ret.push({
                        isInit: 0,
                        transTypeName: '小计',
                        buName: '',
                        buId: bus[j].buId,
                        billNo: '',
                        billDate: '',
                        addYs: sumys,
                        rpAmount: fk,
                        ysAmount: ysAmount,
                        leftYe: leftYe,
                        yufAdd: sumyus,
                        yhAmount: yhAmount,
                        nowCheck: nowCheck,
                        ye: bus[bus.length-1].ye,
                        remark: ''
                    })
                }
            }
        }
        for (let i = 0; i < ret.length; i++) {
            if(i > 0) {
                if(ret[i].isInit === 1) {
                }
            }
        }

        return ctx.suc('', {
            total: 0,
            list: ret
        })
    }

})



module.exports = router;
